import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Test {
    //查找操作
    public static void main(String[] args) throws SQLException {
        DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("cxh0805");
        //连接数据库
        Connection connection= dataSource.getConnection();
        //执行SQL语句
        String sql="select * from student where math>80";
        PreparedStatement statement= connection.prepareStatement(sql);
        ResultSet set= statement.executeQuery();
        while(set.next()){
            int id= set.getInt("id");
            String name=set.getString("name");
            double math= set.getDouble("math");
            double chinese= set.getDouble("chinese");
            double english= set.getDouble("english");
            System.out.println(id+" "+name+" "+math+" "+chinese+" "+english+" ");
        }
        //关闭资源
        statement.close();
        connection.close();
    }

    //删除操作
    public static void main3(String[] args) throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("cxh0805");
        //连接数据库
        Connection connection= dataSource.getConnection();
        //SQL语句
        String sql="delete from student where name='甜甜'";
        PreparedStatement statement= connection.prepareStatement(sql);
        int row = statement.executeUpdate();
        //关闭资源
        statement.close();
        connection.close();
    }
    //修改操作
    public static void main2(String[] args) throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("cxh0805");
        //连接数据库
        Connection connection= dataSource.getConnection();
        //SQL语句
        String sql="update  student set name='琪琪' where name='qiqi'";
        PreparedStatement statement= connection.prepareStatement(sql);
        int row= statement.executeUpdate();
        //关闭。如同栈一般，先开后关
        statement.close();
        connection.close();
    }
    //新增操作
    public static void main1(String[] args) throws SQLException {
        //描述数据库在哪里
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("cxh0805");
        //连接数据库
        Connection connection= dataSource.getConnection();
        //从键盘中输入学号3 ，姓名:琪琪，数学 78，语文90 英语 72.5
        System.out.println("请输入学号，数，语，英三门成绩，姓名");
        Scanner scan=new Scanner(System.in);
        int id=scan.nextInt();
        double math=scan.nextDouble();
        double chinese=scan.nextDouble();
        double english=scan.nextDouble();
        String name=scan.next();
        //编写sql语句
        //String sql = "insert into student values(" + id +",'"+name +"', " +math+"," +chinese+"," +english+")";
        String sql = "insert into student values(?, ?, ?, ?, ?)";
        PreparedStatement statement= connection.prepareStatement(sql);
        statement.setInt(1,id);//id是int,是第一个？代替的。
        statement.setString(2,name);
        statement.setDouble(3,math);
        statement.setDouble(4,chinese);
        statement.setDouble(5,english);
        //sql语句的 增 删 改的语句都是通过executeUpdate()来执行的
        int row= statement.executeUpdate();
        System.out.println(row);
        //关闭连接
        statement.close();
        connection.close();
    }
}
